package com.o2o.etl_data.jd.brandv1.UpdataTable_new.utils

import com.alibaba.fastjson.JSON
import org.apache.spark.sql.{DataFrame, SQLContext, SparkSession}

/**
  * @Description TODO
  * @Author liutaowei
  * @Date 2019-5-5 1:44
  */
object brand_join_util {

  def brand_join(data:DataFrame,spark:SparkSession,result_path:String,sqlContext:SQLContext):DataFrame={
//    将原始数据删除掉品牌部分字段
    /*val source_data = data.drop("brandName_cn","brand_isLaoZiHao","brandName_en","brand_state","brand_type","brandSourceId","_id")
          .na.fill(Map("Base_Info"->"-1"))
//    原始数据
    source_data.registerTempTable("sourcedata")*/


    val mid = data.drop("brandName_cn","brand_isLaoZiHao","brandName_en","brand_state","brand_type","brandSourceId","_id")
      // .na.fill(Map("Base_Info"->StructType(List(StructField("-1", StringType, true)))))
      .toJSON.rdd.map(line =>{
      val lines = JSON.parseObject(line)
      val Base_Info = lines.getOrDefault("Base_Info", "-1").toString
      lines.put("Base_Info",Base_Info)
      lines.toString
    })


    val source_data = sqlContext.read.json(mid)
    //    原始数据
    source_data.registerTempTable("sourcedata")




//    println("原始数据注册表完成！！！")
//    品牌表
    val brand_table = sqlContext.read.json(result_path)
      .where("brandValueId != '-1' ")
      .dropDuplicates("brandCcId")
      .registerTempTable("brandCctable")
//    println("品牌表读取完成")


//这个是关联品牌，内资外资，老字号
    val brandjoin = spark.sqlContext.sql(
      """
        |select
        |t1.*,
        |case when t2.brandCcId is null then upper(t1.brandName) else t2.brandName_cn end cn,
        |case when t2.brandCcId is null then upper(t1.brandName) else t2.brandName_en end en,
        |case when t2.brandCcId is null then '0' else t2.brand_state end state,
        |case when t2.brandCcId is null then '0' else t2.brand_isLaoZihao end lzh,
        |case when t2.brandCcId is null then '-1' else t2.brand_type end type
        |from
        |(select  *,concat(brandValueId,"|",firstCategoryId) brandSourceId
        |from
        |sourcedata ) t1
        |left join
        |brandCctable t2
        |on t1.brandSourceId=t2.brandCcId
      """.stripMargin)
      .drop("brandName_cn", "brandName_en", "brand_isLaoZihao", "brand_state", "brand_type")
      .withColumnRenamed("cn", "brandName_cn")
      .withColumnRenamed("en", "brandName_en")
      .withColumnRenamed("state", "brand_state")
      .withColumnRenamed("type", "brand_type")
      .withColumnRenamed("lzh", "brand_isLaoZihao")
//    brandjoin.cache()
    brandjoin.registerTempTable("brandjoin")

//    println("关联品牌")


    //提取资生堂数据处理
    //资生堂 及子品牌 匹配
    val zishengtangdata = spark.sqlContext.sql(
      """
        |select
        |*
        |from
        |brandjoin
        |where
        |title rlike '资生堂' or brandName rlike '资生堂' or brandName_cn rlike '资生堂'
        |or brandName_cn rlike 'Shiseido'
        |or brandName_cn rlike '宝洁'
        |or brandName rlike 'ALWAYS'
        |or brandName rlike '姬芮'
        |or brandName rlike '蒂思岚'
        |or title rlike '花王'
        |or title rlike 'kao'
        |or title rlike 'Merries'
        |or title rlike '乐而雅'
        |or title rlike '洁霸'
        |or title rlike 'laurier'
        |or title rlike '美舒律'
        |or title rlike 'KAO'
        |or upper(brandName) rlike 'OPPO'
        |or title rlike '妙而舒'
        |or brandName rlike '花王'
        |or brandName rlike 'kao'
        |or brandName rlike 'Merries'
        |or brandName rlike '乐而雅'
        |or brandName rlike '洁霸'
        |or brandName rlike 'laurier'
        |or brandName rlike '美舒律'
        |or brandName rlike 'KAO'
        |or brandName rlike '妙而舒'
        |or brandName_cn rlike '安耐晒'
        |or brandName_cn rlike '纳斯'
        |or brandName_cn rlike '妙而舒'
        |or brandName_cn rlike '尤妮佳'
        |or brandName_cn rlike '安热沙'
        |or brandName_cn rlike '乐互宜'
        |or brandName_cn rlike '大王'
        |or brandName_cn is null
        |or brandName_cn = 'OTHER'
        |or brandName_cn = 'OTHERS'
        |or brandName_cn rlike '见描述'
        |or (brandName_cn rlike '其他' or brandName_cn rlike '其它' or upper(brandName_cn) = 'OTHER')
        |or brandName_cn rlike '无名'
        |or (brandName_cn rlike 'CIPE' and brandName_cn rlike 'RE')
        |or (brandName_cn rlike 'DHC' and thirdCategoryId = '100190201')
        |or (brandName_cn rlike 'FANCL' and thirdCategoryId = '100190201')
        |or (upper(title) rlike 'CAUDALIE' and title rlike '欧缇丽' and thirdCategoryId = '100190102')
        |or (brandName_cn rlike 'EAUPRECIEUSE'  and thirdCategoryId = '100190102')
        |or (brandName_cn = '雕'  and thirdCategoryId = '100220201')
        |or (brandName_cn rlike '萱肌蜜蒸汽眼罩'  and thirdCategoryId = '100251002')
        |or (brandName rlike '灰姑娘'  and thirdCategoryId = '100251002')
        |or (brandName_cn rlike '博朗' or brandName_cn rlike '欧乐'
        |   or brandName rlike '欧乐' or upper(brandName) rlike 'ORAL-B' or brandName_cn rlike 'ORAL-B'
        |   or upper(brandName) rlike 'BRAUN' or brandName_cn rlike 'BRAUN' or brandName rlike '博朗')
        |or (brandName_cn rlike 'M.PASSWORD' or brandName_cn rlike '棉密码')
        |
        |or (firstCategoryId = '10019' and (brandName rlike '爱敬' or upper(brandName) rlike "AGE 20'S" or upper(brandName) rlike 'KERASYS' or upper(brandName) rlike 'AEKYUNG'
        |or  upper(brandName) rlike "AGE20'S" or brandName rlike '爱敬2080牙膏' or brandName rlike '爱纪二十之' or upper(brandName) rlike 'AGE' )
        |and upper(brandName) not rlike 'MAQuillAGE/心机' and upper(brandName) not rlike 'SECRET AGE/秘姿' and upper(brandName) not rlike 'glamourflage/格兰玛弗兰'
        |and upper(brandName) not rlike 'Beauty Cottage' and upper(brandName) not rlike 'AGEbeautiful' and upper(brandName) not rlike 'LUAGE/莉语'
        |and upper(brandName) not rlike 'AGessences' and upper(brandName) not rlike 'Pinkage' and upper(brandName) not rlike 'BOSAGESA/宝尚姿彩'
        |and upper(brandName) not rlike 'BRILLIAGE' and upper(brandName) not rlike 'wellage' and upper(brandName) not rlike 'Keragen'
        |and upper(brandName) not rlike 'NewAge' and upper(brandName) not rlike 'STAGECOLOR' and upper(brandName) not rlike 'TIMAGE/彩棠'
        |and upper(brandName) not rlike 'ansage' and upper(brandName) not rlike 'BioFanAge/博藩' and upper(brandName) not rlike 'ELEVAGE/依莉凡'
        |and upper(brandName) not rlike 'lissage' and upper(brandName) not rlike 'VOYAGE DE TEMPS' and upper(brandName) not rlike 'LAVENDER COTTAGE/薰衣草森林'
        |and upper(brandName) not rlike 'nage peau' and upper(brandName) not rlike '誉康' and upper(brandName) not rlike 'AEKYUNG/露娜'
        |and upper(brandName) not rlike '爱敬雪儿' and upper(brandName) not rlike 'AEKYUNGPOINT/爱敬泊滢特' and upper(brandName) not rlike '梵颜(FOYAGE)'
        |and upper(brandName) not rlike '形象美/images' and upper(brandName) not rlike '奇蹟莊園（AGEDOR）' and upper(brandName) not rlike '麦吉丽（Mageline）'
        |and upper(brandName) not rlike '香村（SCENT VILLAGE）' and upper(brandName) not rlike '秘姿（SECRET AGE）' and upper(brandName) not rlike '施婷姬蔻（Stage Q）'
        |and upper(brandName) not rlike 'MAQUILLAGE' )
        |
        |or upper(brandName) = 'SOLOVE' or (upper(brandName) rlike '鲁茜' or  upper(brandName) = 'RÜSCH') or (upper(brandName) rlike '丹尼尔惠灵顿' or  upper(brandName) rlike 'DANIELWELLINGTON' or  upper(brandName) rlike 'DANIEL WELLINGTON')
        |or brandName_cn rlike '陈克明' or (brandName rlike '华为' or upper(brandName) rlike 'HUAWEI' or brandName_cn rlike '华为' or brandName_cn rlike 'HUAWEI'
        |                                 or brandName_cn rlike '荣耀' or brandName_cn rlike 'HONER' or brandName rlike '荣耀' or upper(brandName) rlike 'HONER'
        |                                 or title rlike '华为' or upper(title) rlike 'HUAWEI' or title rlike '荣耀' or upper(title) rlike 'HONER')
        |or (upper(brandName) rlike 'LENOVO' or brandName rlike '联想' or brandName_cn rlike '联想' or brandName_cn rlike 'LENOVO'
        |   or upper(brandName) rlike 'THINKPAD' or upper(title) rlike 'THINKPAD' or upper(title) rlike 'THINK PAD')
        |or (upper(title) rlike '卡姿兰' and brandName_cn not rlike '资生堂')
        |or (brandName rlike '贝德玛') or (brandName_cn rlike '资生堂' and upper(title) rlike 'MOILIP')
        |or (brandName_cn rlike '姬芮/Za' or upper(brandName) rlike '姬芮/Za') or (brandName rlike '美赞臣' or upper(brandName) rlike 'MEAD JOHNSON' or upper(brandName) rlike 'MEADJOHNSON'
        |or brandName rlike 'Enfamil' or brandName rlike '美强生' or (title rlike '美赞臣' and  brandName rlike 'Johnson\\)'))
        |or ((title rlike '欧莱雅'  or brandName_cn rlike '欧莱雅') and brandName = '-1' and brandName not rlike '小美盒'
        |and brandName not rlike '专业' and brandName not rlike '柏纯' and brandName
        |not rlike '嘉瀛') or  (brandName_cn rlike '欧莱雅' and brandName_cn not rlike '小美盒') or (title rlike '多芬')
        |or (upper(title) rlike '柏兰茜') or (upper(title) rlike 'MILANI')
        |or (brandName_cn rlike '乔丹' or brandName_cn rlike 'JORDAN')
        |or (brandName_cn rlike '百雀羚' or brandName_cn rlike 'PECHOIN')
        |or (brandName = '三生花')
        |
        |--Sun Ozon
        |or (brandName rlike 'Sun Ozon')
        |--Yunos/悠娜媤
        |or (brandName rlike 'Yunos' or brandName rlike '悠娜媤')
        |--Sunozon
        |or (brandName rlike 'Sunozon')
        |--Junopoga/靓采宝嘉
        |or (brandName rlike 'Junopoga' or brandName rlike '靓采宝嘉')
        |--JMsolution
        |or (brandName rlike 'JMsolution')
        |
        |or (brandName_cn rlike '佳丽宝' or brandName_cn rlike '嘉娜宝' or brandName_cn rlike 'KANEBO' or brandName_cn rlike 'KRACIE' or brandName_cn rlike '肌美精')
        |or brandName_cn rlike '美士'
        |
        |or brandName_cn rlike '易果'
        |
        |or brandName = '毕扬'
        |or title rlike 'LG'
        |--安肌心语
        |or title rlike 'd program' or title rlike "D Program" or title rlike "D program" or title rlike "dprogram" or title rlike "d  program"
        |or title rlike "d Program" or title rlike "d-program" or title rlike "D-program" or title rlike "Dprogram" or title rlike "DProgram"
        |or title rlike "D program"or title rlike "D PROGRAM" or title rlike "d　program" or title rlike "DPROGRAM" or title rlike "D Program" or title rlike "d program"
        |
      """.stripMargin)
      .withColumnRenamed("brandId", "aaaa")
      .withColumnRenamed("brandName_cn", "bbbb")
      .withColumnRenamed("brandName_en", "cccc")
    zishengtangdata.registerTempTable("table_shiseido")
//    println("资生堂处理！！")


    /**
      * 这个是根据title和base_info来修改的品牌名称
      */
    val zishengtangdatares1 = spark.sqlContext.sql(
      """
        |select
        |case
        |when upper(brandName) = 'ALWAYS' then '护舒宝'
        |when bbbb rlike '大王' then(case when thirdCategoryId ='100190801' then '怡丽/ELIS' else '大王/GOO．N！' end)
        |when ((bbbb rlike '尤妮佳' or bbbb rlike '乐互宜') and thirdCategoryId ='100200202') then '乐互宜'
        |when ((bbbb rlike '尤妮佳' or bbbb rlike '乐互宜') and thirdCategoryId ='100200201') then '尤妮佳/MOONY'
        |when ((bbbb rlike '尤妮佳' or bbbb rlike '乐互宜') and thirdCategoryId ='100190801') then '苏菲/SOFY'
        |when bbbb rlike '宝洁' then(case
        |when titles rlike 'BOLD' or titles rlike '豪迈' or titles rlike '花香子' then '豪迈/BOLD'
        |when titles rlike '碧浪' or titles rlike 'ARIEL' then '碧浪/ARIEL'
        |when titles rlike '护舒宝' or titles rlike 'ALWAYS' then '护舒宝'
        |when titles rlike 'SARASA' then 'SARASA'
        |when titles rlike 'COSMO' then 'SARASA'
        |else '豪迈/BOLD' end)
        |when title rlike '资生堂' or brandName rlike '资生堂' or bbbb rlike '资生堂'
        |or bbbb rlike 'Shiseido' or brandName rlike '姬芮' or bbbb rlike '安热沙'
        |or bbbb rlike '安耐晒' or bbbb rlike '纳斯'
        |or brandName rlike '蒂思岚' then( case
        |when title rlike '吾诺' or upper(title) rlike 'UNO' and  (title rlike '洗颜' or title rlike '专科' or title rlike '珊珂' or title rlike 'SENKA') then '吾诺/UNO'
        |when brandName rlike '资生堂' and upper(title) rlike 'SK-II' then 'SK-II'
        |when Base_Info rlike '肌肤之钥'or bbbb rlike '肌肤之钥'or titles rlike '肌肤之钥' or titles rlike 'CPB' then '肌肤之钥/CPB'
        |when Base_Info rlike '水之密语'or bbbb rlike '水之密语'or titles rlike '水之密语' or titles rlike 'AQUAIR'  then '水之密语/AQUAIR'
        |when Base_Info rlike '安耐晒'  or Base_Info rlike '安热沙' or bbbb rlike '安耐晒' or titles rlike '安耐晒' or titles rlike 'ANESSA' or titles rlike '安热沙' or bbbb rlike '安耐晒' then '安热沙/ANESSA'
        |when Base_Info rlike '欧珀莱'  or bbbb rlike '欧珀莱' or titles rlike '欧珀莱' or titles rlike 'AUPRES' then '欧珀莱/AUPRES'
        |when Base_Info rlike '蒂思岚'  or bbbb rlike '蒂思岚' or titles rlike '蒂思岚' or titles rlike 'DICILA' then '蒂思岚/DICILA'
        |when Base_Info rlike '怡丽丝尔'or bbbb rlike '怡丽丝尔'or titles rlike '怡丽丝尔' or titles rlike 'ELIXIR' then '怡丽丝尔/ELIXIR'
        |when Base_Info rlike '肌水' or bbbb rlike '肌水' or titles rlike '肌水' or titles rlike 'HADASUI' then '肌水/HADASUI'
        |when Base_Info rlike '美润' or bbbb rlike '美润' or titles rlike '美润' or (titles rlike 'HAND' and titles rlike 'CREAM') then '美润护手霜/HAND CREAM'
        |when Base_Info rlike '茵芙莎' or bbbb rlike '茵芙莎'or titles rlike '茵芙莎' or titles rlike 'IPSA' then '茵芙莎/IPSA'
        |when Base_Info rlike '玛馨妮' or bbbb rlike '玛馨妮'or titles rlike '玛馨妮' or titles rlike 'MACHERIE' or titles rlike '玛宣妮' then '玛馨妮/MACHERIE'
        |when Base_Info rlike '心机' or bbbb rlike '心机' or titles rlike '心机' or titles rlike 'MAQUILLAGE' then '心机彩妆/MAQUILLAGE'
        |when Base_Info rlike '纳斯' or bbbb rlike '纳斯' or titles rlike '纳斯' or titles rlike 'NARS' or titles rlike '娜斯' or bbbb rlike '纳斯' then '娜斯/NARS'
        |when Base_Info rlike '泊美' or bbbb rlike '泊美' or titles rlike '泊美' or (titles rlike 'PURE' and titles rlike 'MILD') then '泊美/PURE&MILD'
        |when Base_Info rlike '洗颜' or bbbb rlike '洗颜' or titles rlike '洗颜' or titles rlike '专科' or titles rlike '珊珂' or titles rlike 'SENKA' then '珊珂/SENKA'
        |when Base_Info rlike '惠润' or bbbb rlike '惠润' or titles rlike '惠润' or (titles rlike 'SUPER' and titles rlike 'MILD') then '惠润/SUPER MILD'
        |when Base_Info rlike '丝蓓' or bbbb rlike '丝蓓' or titles rlike '丝蓓' or titles rlike 'TSUBAKI' then '丝蓓绮/TSUBAKI'
        |when Base_Info rlike '吾诺' or bbbb rlike '吾诺' or titles rlike '吾诺' or titles rlike 'UNO' then '吾诺/UNO'
        |when Base_Info rlike '悠莱' or bbbb rlike '悠莱' or titles rlike '悠莱' or titles rlike 'URARA' then '悠莱/URARA'
        |when Base_Info rlike '姬芮' or bbbb rlike '姬芮' or titles rlike '姬芮' or titles rlike 'ZA' then '姬芮/Za'
        |when Base_Info rlike '嘉珂' or bbbb rlike '嘉珂' or titles rlike '嘉珂' or titles rlike 'JOICO' then '嘉珂/JOICO'
        |when titles rlike 'INTEGRATE' then 'INTEGRATE'
        |when Base_Info rlike 'FINO' or bbbb rlike 'FINO' or titles rlike 'FINO' then 'FINO'
        |when Base_Info rlike '水之印'  or bbbb rlike '水之印'  or titles rlike '水之印' or titles rlike 'AQUALABEL' then '水之印/AQUALABEL'
        |when Base_Info rlike '艾杜纱'  or bbbb rlike '艾杜纱'  or titles rlike '艾杜纱' or titles rlike 'ETTUSAIS' or titles rlike '艾杜紗' then '艾杜纱/ETTUSAIS'
        |when Base_Info rlike '可悠然'  or bbbb rlike '可悠然'  or titles rlike '可悠然' or titles rlike 'KUYURA' then '可悠然/KUYURA'
        |when Base_Info rlike '臻白无瑕' or bbbb rlike '臻白无瑕' or titles rlike '臻白无瑕' or titles rlike 'HAKU'  then '资生堂臻白无瑕/HAKU'
        |when titles rlike 'FRESSY' then 'FRESSY'
        |when titles rlike 'FWB' then 'FWB'
        |when titles rlike 'NATURGO' then 'NATURGO'
        |when titles rlike 'HG' then 'HG'
        |when titles rlike '尿素' then '尿素'
        |when titles rlike 'SEA' or titles rlike 'BREEZE' then 'SEA BREEZE'
        |when titles rlike 'ADENOGEN' then '不老林/ADENOGEN'
        |when titles rlike '不老林' then '不老林/ADENOGEN'
        |when titles rlike 'PLAYLIST' then 'PLAYLIST'
        |when titles rlike '2E' then '2E'
        |else '资生堂/Shiseido' end)
        |when (title rlike '花王'
        |or title rlike 'kao'
        |or title rlike 'Merries'
        |or title rlike '乐而雅'
        |or title rlike '洁霸'
        |or title rlike 'laurier'
        |or title rlike '美舒律'
        |or title rlike 'KAO'
        |or title rlike '妙而舒'
        |or brandName rlike '花王'
        |or brandName rlike 'kao'
        |or brandName rlike 'Merries'
        |or brandName rlike '乐而雅'
        |or brandName rlike '洁霸'
        |or brandName rlike 'laurier'
        |or brandName rlike '美舒律'
        |or brandName rlike 'KAO'
        |or brandName rlike '妙而舒'
        |or bbbb rlike '妙而舒') then(case
        |when thirdCategoryId = '100251002' then '花王/KAO'
        |when secondCategoryId = '1002002' then '花王/MERRIES'
        |when thirdCategoryId = '100190801' then '乐而雅/LAURIER'
        |when thirdCategoryId = '100220201' then '洁霸/ATTACK'
        |else bbbb end)
        |when (brandName rlike '其他' or (brandName rlike '其' and brandName rlike '他')
        | or (brandName rlike '其' and brandName rlike '它')
        | or brandName rlike '其它' or upper(brandName) rlike 'OTHER')  then '其他/OTHER'
        |when (brandName rlike 'CIPE' and brandName rlike 'RE') then '莱斯璧/RECIPE'
        |else bbbb
        |end brandName_cn,
        |case
        |when bbbb rlike '大王' then(case when thirdCategoryId ='100190801' then 'ELIS/怡丽' else 'GOO．N！/大王' end)
        |when ((bbbb rlike '尤妮佳' or bbbb rlike '乐互宜')and thirdCategoryId ='100200202') then '乐互宜'
        |when ((bbbb rlike '尤妮佳' or bbbb rlike '乐互宜')and thirdCategoryId ='100200201') then 'MOONY/尤妮佳'
        |when ((bbbb rlike '尤妮佳' or bbbb rlike '乐互宜')and thirdCategoryId ='100190801') then 'SOFY/苏菲'
        |when bbbb rlike '宝洁' then cccc
        |when title rlike '资生堂' or brandName rlike '资生堂' or bbbb rlike '资生堂'
        |or bbbb rlike 'Shiseido' or brandName rlike '姬芮' or bbbb rlike '安热沙'
        |or bbbb rlike '安耐晒' or bbbb rlike '纳斯'
        |or brandName rlike '蒂思岚' then( case
        |when title rlike '吾诺' or upper(title) rlike 'UNO' and  (title rlike '洗颜' or title rlike '专科' or title rlike '珊珂' or title rlike 'SENKA') then 'UNO/吾诺'
        |
        |
        |when brandName rlike '资生堂' and upper(title) rlike 'SK-II' then 'SK-II'
        |when Base_Info rlike '肌肤之钥'or bbbb rlike '肌肤之钥'or titles rlike '肌肤之钥' or titles rlike 'CPB' then 'CPB/肌肤之钥'
        |when Base_Info rlike '水之密语'or bbbb rlike '水之密语'or titles rlike '水之密语' or titles rlike 'AQUAIR'  then 'AQUAIR/水之密语'
        |when Base_Info rlike '安耐晒' or Base_Info rlike '安热沙' or bbbb rlike '安耐晒' or titles rlike '安耐晒' or titles rlike 'ANESSA' or titles rlike '安热沙' or bbbb rlike '安耐晒' then 'ANESSA/安热沙'
        |when Base_Info rlike '欧珀莱' or bbbb rlike '欧珀莱' or titles rlike '欧珀莱' or titles rlike 'AUPRES' then 'AUPRES/欧珀莱'
        |when Base_Info rlike '蒂思岚' or bbbb rlike '蒂思岚' or titles rlike '蒂思岚' or titles rlike 'DICILA' then 'DICILA/蒂思岚'
        |when Base_Info rlike '怡丽丝尔'or bbbb rlike '怡丽丝尔'or titles rlike '怡丽丝尔' or titles rlike 'ELIXIR' then 'ELIXIR/怡丽丝尔'
        |when Base_Info rlike '肌水' or bbbb rlike '肌水' or titles rlike '肌水' or titles rlike 'HADASUI' then 'HADASUI/肌水'
        |when Base_Info rlike '美润' or bbbb rlike '美润' or titles rlike '美润' or (titles rlike 'HAND' and titles rlike 'CREAM') then 'HAND CREAM/美润护手霜'
        |when Base_Info rlike '茵芙莎'or bbbb rlike '茵芙莎'or titles rlike '茵芙莎' or titles rlike 'IPSA' then 'IPSA/茵芙莎'
        |when Base_Info rlike '玛馨妮'or bbbb rlike '玛馨妮'or titles rlike '玛馨妮' or titles rlike 'MACHERIE' or titles rlike '玛宣妮' then 'MACHERIE/玛馨妮'
        |when Base_Info rlike '心机' or bbbb rlike '心机' or titles rlike '心机' or titles rlike 'MAQUILLAGE' then 'MAQUILLAGE/心机彩妆'
        |when Base_Info rlike '纳斯' or bbbb rlike '纳斯' or titles rlike '纳斯' or titles rlike 'NARS' or titles rlike '娜斯' or bbbb rlike '纳斯' then 'NARS/娜斯'
        |when Base_Info rlike '泊美' or bbbb rlike '泊美' or titles rlike '泊美' or (titles rlike 'PURE' and titles rlike 'MILD') then 'PURE&MILD/泊美'
        |when Base_Info rlike '洗颜' or bbbb rlike '洗颜' or titles rlike '洗颜' or titles rlike '专科' or titles rlike '珊珂' or titles rlike 'SENKA' then 'SENKA/珊珂'
        |when Base_Info rlike '惠润' or bbbb rlike '惠润' or titles rlike '惠润' or (titles rlike 'SUPER' and titles rlike 'MILD') then 'SUPER MILD/惠润'
        |when Base_Info rlike '丝蓓' or bbbb rlike '丝蓓' or titles rlike '丝蓓' or titles rlike 'TSUBAKI' then 'TSUBAKI/丝蓓绮'
        |when Base_Info rlike '吾诺' or bbbb rlike '吾诺' or titles rlike '吾诺' or titles rlike 'UNO' then 'UNO/吾诺'
        |when Base_Info rlike '悠莱' or bbbb rlike '悠莱' or titles rlike '悠莱' or titles rlike 'URARA' then 'URARA/悠莱'
        |when Base_Info rlike '姬芮' or bbbb rlike '姬芮' or titles rlike '姬芮' or titles rlike 'ZA' then 'Za/姬芮'
        |when Base_Info rlike '嘉珂' or bbbb rlike '嘉珂' or titles rlike '嘉珂' or titles rlike 'JOICO' then 'JOICO/嘉珂'
        |when titles rlike 'INTEGRATE' then 'INTEGRATE'
        |when Base_Info rlike 'FINO' or bbbb rlike 'FINO' or titles rlike 'FINO' then 'FINO'
        |when Base_Info rlike '水之印'  or bbbb rlike '水之印'  or titles rlike '水之印' or titles rlike 'AQUALABEL' then 'AQUALABEL/水之印'
        |when Base_Info rlike '艾杜纱'  or bbbb rlike '艾杜纱'  or titles rlike '艾杜纱' or titles rlike 'ETTUSAIS' or titles rlike '艾杜紗' then 'ETTUSAIS/艾杜纱'
        |when Base_Info rlike '可悠然'  or bbbb rlike '可悠然'  or titles rlike '可悠然' or titles rlike 'KUYURA' then 'KUYURA/可悠然'
        |when Base_Info rlike '臻白无瑕' or bbbb rlike '臻白无瑕' or titles rlike '臻白无瑕' or titles rlike 'HAKU'  then 'HAKU/资生堂臻白无瑕'
        |when titles rlike 'FRESSY' then 'FRESSY'
        |when titles rlike 'FWB' then 'FWB'
        |when titles rlike 'NATURGO' then 'NATURGO'
        |when titles rlike 'HG' then 'HG'
        |when titles rlike '尿素' then '尿素'
        |when titles rlike 'SEA' or titles rlike 'BREEZE' then 'SEA BREEZE'
        |when titles rlike 'ADENOGEN' then 'ADENOGEN/不老林'
        |when titles rlike '不老林' then 'ADENOGEN/不老林'
        |when titles rlike 'PLAYLIST' then 'PLAYLIST'
        |when titles rlike '2E' then '2E'
        |else 'Shiseido/资生堂' end)
        |
        |
        |when (title rlike '花王'
        |or title rlike 'kao'
        |or title rlike 'Merries'
        |or title rlike '乐而雅'
        |or title rlike '洁霸'
        |or title rlike 'laurier'
        |or title rlike '美舒律'
        |or title rlike 'KAO'
        |or title rlike '妙而舒'
        |or brandName rlike '花王'
        |or brandName rlike 'kao'
        |or brandName rlike 'Merries'
        |or brandName rlike '乐而雅'
        |or brandName rlike '洁霸'
        |or brandName rlike 'laurier'
        |or brandName rlike '美舒律'
        |or brandName rlike 'KAO'
        |or brandName rlike '妙而舒'
        |or bbbb rlike '妙而舒')
        |then(case
        |when thirdCategoryId = '100    ' then 'KAO/花王'
        |when secondCategoryId = '1002002' then 'MERRIES/花王'
        |when thirdCategoryId = '100220201' then 'ATTACK/洁霸'
        |when thirdCategoryId = '100190801' then 'LAURIER/乐而雅'
        |
        |else cccc end)
        |when (brandName rlike '其他' or (brandName rlike '其' and brandName rlike '他')
        | or (brandName rlike '其' and brandName rlike '它')
        | or brandName rlike '其它' or upper(brandName) rlike 'OTHER')  then 'OTHER/其他'
        |when (brandName rlike 'CIPE' and brandName rlike 'RE') then 'RECIPE/莱斯璧'
        |else cccc
        |end brandName_en,
        |*
        |from
        |(
        |select
        |*,
        |upper(title) titles
        |from
        |table_shiseido
        |)
      """.stripMargin).drop("titles", "aaaa", "bbbb", "cccc", "unite_brand", "brandSourceId")
      .withColumnRenamed("bbbb", "brandName_cn")
      .withColumnRenamed("cccc", "brandName_en")
    zishengtangdatares1.registerTempTable("zishengtangdatares1")
//    println("从title 和 base_z中处理  资生堂处理！！")


    /**
      * 修改品牌的【这个是根据三级ID进行修改的品牌名称】
      */
    val titledata = spark.sqlContext.sql(
      """
        |select
        |*,
        |case
        |when brandName_cn rlike 'DHC' and thirdCategoryId = '100190201'  then "蝶翠诗/DHC"
        |when brandName_cn rlike 'FANCL' and thirdCategoryId = '100190201'  then "芳珂/FANCL"
        |when (upper(title) rlike 'CAUDALIE' or title rlike '欧缇丽') and thirdCategoryId = '100190102' then "欧缇丽/CAUDALIE"
        |when brandName_cn rlike 'EAUPRECIEUSE'  and thirdCategoryId = '100190102' then "EAU PRECIEUSE"
        |when brandName_cn = '雕'  and thirdCategoryId = '100220201' then "雕牌"
        |when brandName_cn rlike '萱肌蜜蒸汽眼罩'  and thirdCategoryId = '100251002' then "萱肌蜜/CIENCIMY"
        |when brandName rlike '灰姑娘'  and thirdCategoryId = '100251002' then "灰姑娘"
        |
        |--博朗、欧乐B
        |when thirdCategoryId = '100140408' and (brandName_cn rlike '博朗' or brandName_cn rlike '欧乐' or upper(brandName) rlike 'ORAL-B' or upper(brandName) rlike 'BRAUN') then '欧乐B/ORAL-B'
        |when thirdCategoryId != '100140408' and  (brandName rlike '博朗' or upper(brandName) rlike 'BRAUN') then '博朗/BRAUN'
        |when thirdCategoryId != '100140408' and  (brandName rlike '欧乐' or upper(brandName) rlike 'ORAL-B') then '欧乐B/ORAL-B'
        |when thirdCategoryId = '100190702'  and (brandName_cn rlike '欧乐B' or brandName_cn = 'ORAL-B' )  then "欧乐B/ORAL-B"
        |
        |when thirdCategoryId = '100190801' and  (brandName_cn rlike 'M.PASSWORD' or brandName_cn rlike '棉密码')   then '棉密码/M.PASSWORD'
        |
        |--爱敬
        |when thirdCategoryId = '100190202' and (brandName rlike '爱敬' or upper(brandName) rlike "AGE 20'S" or upper(brandName) rlike 'KERASYS' or upper(brandName) rlike 'AEKYUNG'
        |or upper(brandName) rlike "AGE20'S" or brandName rlike '爱敬2080牙膏' or brandName rlike '爱纪二十之' or upper(brandName) rlike 'AGE') then "爱敬/AGE 20'S"
        |when secondCategoryId = '1001904' and (brandName rlike '爱敬' or upper(brandName) rlike "AGE 20'S" or upper(brandName) rlike 'KERASYS' or upper(brandName) rlike 'AEKYUNG'
        |or upper(brandName) rlike "AGE20'S" or brandName rlike '爱敬2080牙膏' or brandName rlike '爱纪二十之' or upper(brandName) rlike 'AGE') then '爱敬/KERASYS'
        |when thirdCategoryId != '100190202' and secondCategoryId != '1001904' and (brandName rlike '爱敬' or upper(brandName) rlike "AGE 20'S" or upper(brandName) rlike 'KERASYS' or upper(brandName) rlike 'AEKYUNG'
        |or upper(brandName) rlike "AGE20'S" or brandName rlike '爱敬2080牙膏' or brandName rlike '爱纪二十之' or upper(brandName) rlike 'AGE') then '爱敬/AEKYUNG'
        |--oppo
        |when thirdCategoryId = '100150101' and upper(brandName) = 'OPPO' then 'OPPO'
        |
        |--百雀羚/PECHOIN
        |when firstCategoryId = '10019' and (upper(brandName) rlike '百雀羚' or  upper(brandName) = 'PECHOIN')   then '百雀羚/PECHOIN'
        |when firstCategoryId = '10019' and (upper(brandName) = '三生花' )   then '百雀羚/PECHOIN'
        |--妮维雅
        |when firstCategoryId = '10019' and (upper(brandName) rlike '妮维雅' or  upper(brandName) = 'NIVEA')   then '妮维雅/NIVEA'
        |
        |when thirdCategoryId = '100200201' and upper(brandName) = 'SOLOVE'    then 'SOLOVE'
        |when secondCategoryId = '1002003' and (upper(brandName) rlike '鲁茜' or  upper(brandName) = 'RÜSCH')   then '鲁茜/RÜSCH'
        |when secondCategoryId = '1001703' and (upper(brandName) rlike '丹尼尔惠灵顿' or  upper(brandName) rlike 'DANIELWELLINGTON' or  upper(brandName) rlike 'DANIEL WELLINGTON')  then "丹尼尔惠灵顿/DANIEL WELLINGTON"
        |when secondCategoryId = '1002102' and brandName_cn rlike '陈克明'   then '陈克明/CKM'
        |
        |--title 华为荣耀
        |when (firstCategoryId = '10014' or  firstCategoryId = '10015' or  firstCategoryId = '10016') and (brandName rlike '荣耀' or upper(brandName) = 'HONOR') then '荣耀/HONOR'
        |when (firstCategoryId = '10014' or  firstCategoryId = '10015' or  firstCategoryId = '10016') and (brandName rlike '华为' or upper(brandName) = 'HUAWEI' or brandName_cn rlike '华为' or brandName_cn = 'HUAWEI')
        |then
        |(
        |case
        |when title rlike '荣耀' or upper(title) rlike 'HONOR'  then '荣耀/HONOR'
        |when firstCategoryId = '10016'  and (title rlike '华为' or upper(title) rlike 'HUAWEI' and title rlike '王者荣耀') then '华为/HUAWEI'
        |when firstCategoryId = '10016' and ((title rlike '荣耀' or upper(title) rlike 'HONOR') and title not rlike '王者') then '荣耀/HONOR'
        |else brandName_cn end
        |)
        |when (firstCategoryId = '10014' or  firstCategoryId = '10015' or  firstCategoryId = '10016') and (brandName rlike '华为' or upper(brandName) = 'HUAWEI') then '华为/HUAWEI'
        |
        |--联想、THINKPAD
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (upper(brandName) rlike 'LENOVO' or brandName rlike '联想' or brandName_cn rlike 'LENOVO' or brandName_cn rlike '联想')
        |then (case when upper(brandName) rlike 'THINKPAD' or upper(title) rlike 'THINKPAD' or upper(title) rlike 'THINK PAD'  then 'THINKPAD' else brandName_cn end)
        |when (firstCategoryId = '10014' or firstCategoryId = '10015' or firstCategoryId = '10016') and (upper(brandName) rlike 'LENOVO' or brandName rlike '联想' or brandName_cn rlike 'LENOVO' or brandName_cn rlike '联想') then '联想/LENOVO'
        |
        |
        |when firstCategoryId = '10019'  and upper(title) rlike '卡姿兰' and brandName_cn not rlike '资生堂' then '卡姿兰/CARSLAN'
        |when firstCategoryId = '10019'  and brandName rlike '贝德玛' then
        |(case when upper(title) rlike '贝德玛' and brandName_cn not rlike '资生堂' then '贝德玛/BIODERMA' else brandName_cn end)
        |when firstCategoryId = '10019' and (brandName_cn rlike '资生堂' and upper(title) rlike 'MOILIP') then 'MOILIP'
        |--姬芮
        |when firstCategoryId = '10019' and (brandName_cn rlike '姬芮/Za' or upper(brandName) rlike '姬芮/Za') then
        |(case when
        |(upper(title) rlike 'THE GINZA' or upper(title) rlike 'THE  GINZA'
        |or upper(title) rlike 'THE/GINZA'
        |or upper(brandName) rlike 'THE GINZA' or upper(title) rlike 'GINZA') then 'THE GINZA' else brandName_cn end)
        |
        |--美赞臣
        |when firstCategoryId = '10021' and (brandName rlike '美赞臣' or upper(brandName) rlike 'MEAD JOHNSON' or upper(brandName) rlike 'MEADJOHNSON'
        | or brandName rlike 'Enfamil' or brandName rlike '美强生' or (title rlike '美赞臣' and  brandName rlike 'Johnson\\)')) then '美赞臣/MEAD JOHNSON'
        |--欧莱雅
        |when (title rlike '欧莱雅'  or brandName_cn rlike '欧莱雅') and brandName = '-1' and brandName not rlike '小美盒'
        |and brandName not rlike '专业' and brandName not rlike '柏纯' and brandName
        |not rlike '嘉瀛' then "欧莱雅/L'OREAL"
        |when brandName_cn rlike '欧莱雅' and brandName_cn not rlike '小美盒' then "欧莱雅/L'OREAL"
        |when firstCategoryId = '10019'  and title rlike '多芬' and brandName = '-1' then "多芬/DOVE"
        |when firstCategoryId = '10019'  and upper(title) rlike '柏兰茜' and brandName_cn not rlike '资生堂' then '柏兰茜/BOLANXI'
        |when firstCategoryId = '10019'  and upper(title) rlike 'MILANI' and brandName_cn not rlike '资生堂' then '米兰妮/MILANI'
        |--哈罗闪SANOSAN
        |when thirdCategoryId = '100200402'  and (brandName rlike '哈罗闪' or upper(brandName) rlike 'SANOSAN') then '哈罗闪/SANOSAN'
        |--妈妈宝贝（mama&kids）  MAMA&KIDS
        |when thirdCategoryId = '100200402'  and  brandName rlike '妈妈宝贝'  then 'MAMA&KIDS'
        |--润本/RUNBEN
        |when thirdCategoryId = '100200402'  and (brandName rlike '润本' or upper(brandName) rlike 'RUNBEN') then '润本/RUNBEN'
        |--GOAT SOAP
        |when thirdCategoryId = '100200401'  and brandName = 'GOAT SOAP'  then 'GOAT SOAP'
        |--佳丽宝 肌美精
        |when
        |brandName_cn rlike '佳丽宝' or brandName_cn rlike '嘉娜宝' or brandName_cn rlike 'KANEBO' or brandName_cn rlike 'KRACIE' or brandName_cn rlike '肌美精'
        |then
        |(case when
        |firstCategoryId = '10019'   and  (brandName rlike '佳丽宝' or upper(brandName) rlike 'KANEBO' or brandName rlike '嘉娜宝'
        |or upper(brandName) rlike 'SUISAI' or upper(brandName) rlike 'KANEBO SENSAI')
        |and upper(brandName) != 'KRACIE' and brandName not rlike '肌美精' then '佳丽宝/KANEBO'
        |when firstCategoryId = '10019'   and (brandName rlike '肌美精' or upper(brandName) rlike 'KRACIE')
        |and upper(brandName) != 'KANEBO' and brandName not rlike '嘉娜宝' and brandName not rlike '佳丽宝' then '肌美精/KRACIE'
        |when firstCategoryId = '10019' and  upper(brandName) rlike 'ALLIE' then 'ALLIE'
        |when firstCategoryId = '10019' and  upper(brandName) rlike '雅呵雅' then '雅呵雅丝睿/AQUASPRINA'
        |when firstCategoryId = '10019' and  upper(brandName) rlike 'AQUA SPRINA' then '雅呵雅丝睿/AQUASPRINA'
        |when firstCategoryId = '10019' and  (brandName = 'Kracie' or brandName = '肌美精' or brandName = 'Kracie 肌美精') then '肌美精/KRACIE'
        |else brandName_cn end)
        |
        |--newAdd [2019_1]
        |when firstCategoryId = '10021' and
        |brandName_cn rlike "易果" then (case
        |when title rlike "伊利" then "伊利/YILI"
        |when title rlike "蒙牛" then "蒙牛/MENGNIU"
        |when title rlike "卡士" then "卡士/CLASSY.KISS" else brandName_cn end)
        |--毕扬
        |when secondCategoryId = "1001901" and brandName = "毕扬" then "毕扬/b´Yeswo"
        |--LG品牌拆分
        |--when firstCategoryId = '10019' and brandName = 'LG' then (case
        |--when title rlike '竹盐' or title rlike 'LG液体按压式牙膏' or title rlike 'LG倍瑞傲' then '竹盐/LG')
        |--when title rlike '睿嫣' then '睿嫣/LG')
        |--when title rlike 'LG ONTHEBODY' or title rlike "LG ON THE BODY"  or title rlike "LG ON" or title rlike "LGON"
        |--or title rlike "安宝笛" or title rlike "韩国进口ON" then "安宝笛/ON THE BODY"
        |--when title rlike "菲诗小铺" or title rlike "LG秘密花园" then "菲诗小铺/THE FACE SHOP"
        |--when title rlike "LG Perio" then "LG PERIO"
        |--when title rlike "贵爱娘" then "贵爱娘" else brandName_cn end)
        |--安肌心语
        |when firstCategoryId = '10019' then (case
        |when title rlike 'd program' or title rlike "D Program" or title rlike "D program" or title rlike "dprogram" or title rlike "d  program"
        |or title rlike "d Program" or title rlike "d-program" or title rlike "D-program" or title rlike "Dprogram" or title rlike "DProgram"
        |or title rlike "D program"or title rlike "D PROGRAM" or title rlike "d　program" or title rlike "DPROGRAM" or title rlike "D Program" or title rlike "d program"
        |then "安肌心语/d program" else brandName_cn end)
        |--沐浴下面
        |when thirdCategoryId = '100190601' and title rlike "LG" then (case
        |when title rlike '欧活ORGANIST' then '欧活/ORGANIST'
        |when title rlike 'Wonder bath' then 'wonder bath'
        |when title rlike '心灵小屋' then '心灵小屋'
        |else '安宝笛/ON THE BODY' end)
        |
        |--乔丹
        |when brandName_cn rlike '乔丹' or brandName_cn rlike 'JORDAN' then
        |(case
        |when upper(brandName) = 'AIR JORDAN' or upper(brandName) = 'ARI JORDAN' or upper(brandName) = 'JORDAN' or upper(brandName) rlike 'NIKE AIR JORDAN' or brandName rlike '美国乔丹'
        |or upper(brandName) rlike 'ALEXIS JORDAN' or good_id = '560743988807'
        |or good_id = '578470198069'
        |or good_id = '576261123547' or good_id = '578463726257' or brandName rlike 'JORDAN（洗护）' or brandName_cn = 'JORDAN BRAND' then 'JORDAN'
        |when brandName = '乔丹' or brandName = '乔丹（QIAODAN）' or brandName = '乔丹体育' or brandName rlike '乔丹\\(MCR JOON\\)'
        |or brandName rlike '乔丹（中国）'  or brandName rlike 'QIAODAN' or brandName rlike '乔丹A361' or brandName = '—乔丹—' or brandName = '乔丹23号'
        |or brandName = '乔丹中国' or brandName = 'qiaodan' then '乔丹'
        |when brandName rlike '乔丹格兰' or brandName rlike 'JOEDON GOLAN' then '乔丹格兰/JOEDON GOLAN'
        |when brandName rlike '里奇乔丹' then '里奇乔丹/RICKY JORDAN'
        |when brandName rlike '英乔丹拉莫斯' then '英乔丹拉莫斯'
        |when brandName rlike '哈雷乔丹' then '哈雷乔丹'
        |when brandName rlike '强劲乔丹' then '强劲乔丹/QJQD'
        |when brandName rlike '麦迪乔丹' then '麦迪乔丹'
        |when brandName rlike '斯乔丹蒂' then '斯乔丹蒂'
        |when brandName rlike '绝世乔丹' then '绝世乔丹'
        |when brandName rlike '乔丹米勒' then '乔丹米勒'
        |when brandName rlike '乔丹公仔' then '乔丹公仔'
        |when brandName rlike '乔丹传人' then '乔丹传人'
        |when brandName rlike '乔丹传奇' then '乔丹传奇'
        |when brandName rlike '乔丹王国' then '乔丹王国'
        |when brandName rlike '乔丹阿玛尼' then '乔丹阿玛尼'
        |when brandName rlike '飞人乔丹' then '飞人乔丹'
        |when brandName rlike '佐敦朱迪' then '佐敦朱迪/JORDAN&JUDY'
        |when brandName rlike '乔登-诗丹' then '乔登-诗丹/JORDAN-CEADAR'
        |else brandName_cn end)
        |
        |
        |when brandName_cn rlike '见描述'  then '-1'
        |when brandName_cn rlike '其他' or brandName_cn rlike '其它' or upper(brandName_cn) = 'OTHER'  then '其他/OTHER'
        |when brandName_cn rlike '无名' then '-1'
        |
        |
        |
        |else brandName_cn end cn
        |from
        |zishengtangdatares1
      """.stripMargin)
      .drop("brandName_cn", "brandName_en")
      .withColumnRenamed("cn", "brandName_cn")
    titledata.registerTempTable("zishengtangdatares2")
//    println("根据三级行业修改数据")

    val datasres =  spark.sqlContext.sql(
      """
        |select
        |*,
        |case when split(brandName_cn,"/")[1] is not null or brandName_cn rlike '/'
        |then concat(upper(split(brandName_cn,"/")[1]),"/",upper(split(brandName_cn,"/")[0]))
        |else upper(brandName_cn) end brandName_en
        |from
        |zishengtangdatares2
      """.stripMargin)
//    datasres.cache()
    datasres.registerTempTable("datasres")

    //总数据与资生堂数据合并-------------------------------------------------------------------
    val zishengtangdatares2 = spark.sqlContext.sql(
      """
        |select
        |t1.*,
        |case when t2.good_id is null then t1.brandName_cn else t2.brandName_cn end cn,
        |case when t2.good_id is null then t1.brandName_en else t2.brandName_en end en,
        |case when t2.good_id is null then t1.brand_state else t2.brand_state end state,
        |case when t2.good_id is null then t1.brand_type else t2.brand_type end type,
        |case when t2.good_id is null then t1.brand_isLaoZiHao else t2.brand_isLaoZiHao end laozihao
        |from
        |brandjoin t1
        |left join
        |datasres t2
        |on t1.good_id = t2.good_id
      """.stripMargin).drop("brandName_cn", "brandName_en", "brand_state", "brand_type", "brand_isLaoZiHao")
      .withColumnRenamed("cn", "brandName_cn")
      .withColumnRenamed("en", "brandName_en")
      .withColumnRenamed("state", "brand_state")
      .withColumnRenamed("type", "brand_type")
      .withColumnRenamed("laozihao", "brand_isLaoZiHao")
//    println("合并数据")

    val zishengtangdatares = zishengtangdatares2.cache()
    zishengtangdatares
  }
}
